package com.liwen.kefu;

import com.liwen.kefu.utils.JdbcUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Before;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author: Liwen
 * @createTime: 2024/10/23
 * @description:
 */

@SpringBootTest
@RunWith(SpringRunner.class)
@Slf4j
public class ImportData {
    String host = "10.152.37.9";
    String port = "13316";
    String userName = "root";
    String password = "123456";

    @Resource(name = "hiveJdbcTemplate")
    private JdbcTemplate hiveJdbcTemplate;

    String deleteKey = "";
    String querySql = "";

    String dbName = "db_gmcf_dec";
    String tableName = "t_dec_debit";
    String path = "C:\\Users\\Administrator\\Downloads\\";
    String userId = "000009e2fc8f11e9bd5702000a8f581d";
    String mobileNo = "13306339267";
    String modelName = "新核心-";
    String fileName = "3." + modelName + mobileNo + "-" + tableName;

    String filePath = path + "\\" + fileName + ".xlsx";
    Map<String, String> sqlMap = new HashMap<>();
    Map<String, String> baseNewMap = new HashMap<>();

    @Before
    public void initIds() {
        HashMap<String, String> idsMap = new HashMap<>();
        idsMap.put("ccsdb.ccs_customer", "cust_id");
        idsMap.put("ccsdb.ccs_acct", "acct_nbr");
        idsMap.put("ccsdb.ccs_loan", "acct_nbr");
        idsMap.put("ccsdb.ccs_plan", "acct_nbr");
        idsMap.put("ccsdb.ccs_order", "acct_nbr");
        idsMap.put("ccsdb.ccs_order", "acct_nbr");
        idsMap.put("ccsdb.ccs_order", "acct_nbr");
        idsMap.put("ccsdb.ccs_asset_sell_info", "f_contr_nbr");
        // 新核心
        idsMap.put("db_gmcf_ofc.t_ofc_loan_order", "f_user_id");
        idsMap.put("db_gmcf_bec.t_bec_bidding", "f_bidding_no");
        idsMap.put("db_gmcf_dec.t_dec_debit", "f_bidding_no");
        idsMap.put("db_gmcf_lsc.t_lsc_repayment", "f_bidding_no");
        idsMap.put("db_gmcf_lsc.t_lsc_repayment", "f_bidding_no");
        idsMap.put("db_gmcf_rec.t_rec_right", "f_bidding_no");
        // 资方
        idsMap.put("db_gmcf_bec.t_bec_investor", "f_bidding_no");
        // 订单级别归属状态
        idsMap.put("ermasdb.ermas_case_main", "acct_nbr");

        deleteKey = idsMap.get(dbName + "." + tableName);
    }

    @Before
    public void initSql() {

        String ofcLoanOrderSql = "select *  from odsdb_gmcf_ofc.t_ofc_loan_order where f_user_id in ('${user_id}') and f_bidding_no <> 'NULL' order by f_update_time desc";
        sqlMap.put("t_ofc_loan_order", ofcLoanOrderSql);
        String becBiddingSql = "select * from odsdb_gmcf_bec.t_bec_bidding where f_bidding_no in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_bec_bidding", becBiddingSql);
        String decDebitSql = "select * from odsdb_gmcf_dec.t_dec_debit where f_bidding_no in (\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_dec_debit", decDebitSql);
        String lscRepaymentSql = "select * from odsdb_gmcf_lsc.t_lsc_repayment where f_bidding_no in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_lsc_repayment", lscRepaymentSql);
        String recRightSql = "select * from odsdb_gmcf_rec.t_rec_right where f_bidding_no in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_rec_right", recRightSql);
        String becInvestorSql = "select * from odsdb_gmcf_bec.t_bec_investor where f_bidding_no in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_bec_investor", becInvestorSql);
        String ermasCaseMainSql = "select * from ermasdb.ermas_case_main ecm where CONTR_NBR in(\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by create_time DESC";
        sqlMap.put("ermas_case_main", ermasCaseMainSql);
        String becExpandRecordSq = "SELECT * FROM odsdb_gmcf_bec.t_bec_expand_record where f_bidding_no in (\n" +
                "    select f_bidding_no  from odsdb_gmcf_ofc.t_ofc_loan_order \n" +
                "    where f_user_id in ('${user_id}')\n" +
                "    and f_bidding_no <> 'NULL'\n" +
                ") order by f_created_time DESC";
        sqlMap.put("t_bec_expand_record", becExpandRecordSq);
        // 老核心
        String ccsCustomerSql = "select * from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"";
        sqlMap.put("ccs_customer", ccsCustomerSql);
        String ccsAcctSql = "select * from  ccsdb.ccs_acct where cust_id in(\n" +
                "    select cust_id from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"\n" +
                ");";
        sqlMap.put("ccs_acct", ccsAcctSql);
        String ccsLoanSql = "select * from ccsdb.ccs_loan where acct_nbr in (\n" +
                "    select acct_nbr from  ccsdb.ccs_acct where cust_id in(\n" +
                "        select cust_id from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"\n" +
                "    )\n" +
                ");";
        sqlMap.put("ccs_loan", ccsLoanSql);
        String ccsPlanSql = "select * from ccsdb.ccs_plan where acct_nbr in(\n" +
                "    select acct_nbr from  ccsdb.ccs_acct where cust_id in(\n" +
                "        select cust_id from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"\n" +
                "    )\n" +
                ");";
        sqlMap.put("ccs_plan", ccsPlanSql);
        String ccsOrder = "select * from ccsdb.ccs_order where acct_nbr in(\n" +
                "    select acct_nbr from  ccsdb.ccs_acct where cust_id in(\n" +
                "        select cust_id from ccsdb.ccs_customer where mobile_no=\"${mobile_no}\"\n" +
                "    )\n" +
                ");";
        sqlMap.put("ccs_order", ccsOrder);

        String sql = sqlMap.get(tableName);
        if (StringUtils.isEmpty(sql)) {
            throw new NullPointerException("map中未找到:" + tableName + "的查询sql");
        }
        String sqlFormat = sql.replaceAll("\\$\\{user_id\\}", userId).replaceAll("\\$\\{mobile_no\\}", mobileNo);
        querySql = sqlFormat;

        baseNewMap.put("db_gmcf_ofc", "t_ofc_loan_order");
        baseNewMap.put("db_gmcf_bec", "t_bec_bidding");
        baseNewMap.put("db_gmcf_dec", "t_dec_debit");
        baseNewMap.put("db_gmcf_lsc", "t_lsc_repayment");
        baseNewMap.put("db_gmcf_rec", "t_rec_right");
        baseNewMap.put("db_gmcf_bec", "t_bec_investor");
    }


    /**
     * 新老核心基础数据一键导入
     */
    @Test
    public void oneAllImport() {
        final int[] num = {1};
        baseNewMap.forEach((db, table) -> {
            dbName = db;
            tableName = table;
            String sql = sqlMap.get(table);
            String sqlFormat = sql.replaceAll("\\$\\{user_id\\}", userId).replaceAll("\\$\\{mobile_no\\}", mobileNo);
            querySql = sqlFormat;
            fileName = num[0] + "." + modelName + mobileNo + "-" + tableName;
            filePath = path + "\\" + fileName + ".xlsx";
            log.info("第：{}次，hive 查询sql：{}", num, querySql);
            List<Map<String, Object>> list = hiveJdbcTemplate.queryForList(querySql);
            log.info("hive 查询完成:{} ，条数：{}", fileName, list.size());
            if (CollectionUtils.isEmpty(list)) {
                log.error("{}，hive 查询结果=null", table);
            }
            try {
                exportDataToExcel(list);
            } catch (Exception e) {
                e.printStackTrace();
            }
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            try {
                excelImportMysql();
            } catch (IOException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            num[0]++;
        });
        JdbcUtil.close();


    }


    /**
     * 单表hive查询
     *
     * @throws Exception
     */
    @Test
    public void hiveQuery() throws Exception {
        log.info("hive 查询sql：{}", querySql);
        List<Map<String, Object>> list = hiveJdbcTemplate.queryForList(querySql);
        log.info("hive 查询完成:{} ，条数：{}", fileName, list.size());
        if (CollectionUtils.isEmpty(list)) {
            throw new NullPointerException("查询hive list ==空！");
        }
        exportDataToExcel(list);
        Thread.sleep(2000);
        excelImportMysql();
    }


    /**
     * 单表excel导入mysql
     */
    @Test
    public void excelDataImport() {
        try {
            excelImportMysql();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }


    /**
     * 单表读取excel，根据deleteKey删除
     *
     * @throws Exception
     */
    @Test
    public void delMysqlByExcel() throws Exception {
        String ids = getIdsByKey();
        execSqlDelete(ids);
    }

    /**
     * excel导入mysql
     */
    public void excelImportMysql() throws IOException, SQLException {
        String ids = getIdsByKey();
        execSqlDelete(ids);
        execInsert(filePath, dbName, tableName);
        queryImportResult(deleteKey, ids);
    }

    /**
     * 查询mysql导入结果
     *
     * @param key
     * @param ids
     */
    public void queryImportResult(String key, String ids) {
        log.info("开始查询导入结果========================================");
        String sql = "SELECT * FROM " + dbName + "." + tableName + " WHERE " + key + " IN(%s)";
        String formatSql = String.format(sql, ids);
        log.info("查询SQL：{}", formatSql);
        List<String> resultList = JdbcUtil.executeQuery(host, port, userName, password, dbName, formatSql, key);
        if (CollectionUtils.isEmpty(resultList)) {
            log.error("查询结果为空：key:{},resultList:{}", key, resultList);
        } else {
            log.info("查询key:{},查询条数：{}", key, resultList.size());
            log.info("查询结果：resultList：{}", resultList);
        }

    }

    /**
     * 获取删除key的value
     *
     * @return
     * @throws IOException
     */
    public String getIdsByKey() throws IOException {
        List<String> excelSqlData = readExcelSqlData(filePath);
        String titles = excelSqlData.get(0);
        String[] split = titles.split(",");
        int index = 0;
        for (int i = 0; i < split.length; i++) {
            if (split[i].contains(deleteKey)) {
                index = i;
                break;
            }
        }
        List<String> idList = new ArrayList<>();
        excelSqlData.remove(0);
        for (String item : excelSqlData) {
            String[] split1 = item.split(",");
            idList.add(split1[index]);
        }
        List<String> distinctIdList = idList.stream().distinct().collect(Collectors.toList());
        String ids = String.join(",", distinctIdList);
        log.info("索引是：{}", index);
        log.info("删除的key：{},value:{}", deleteKey, ids);
        return ids;
    }

    /**
     * 根据key删除sql数据
     *
     * @param ids
     * @throws IOException
     */
    public void execSqlDelete(String ids) throws SQLException {
        log.info("开始删除========================================================");
        String deleteSql = "DELETE FROM " + dbName + "." + tableName + " WHERE " + deleteKey + " IN(%s)";
        String formatSql = String.format(deleteSql, ids);
        log.info("删除sql：{}", formatSql);
        int count = JdbcUtil.executeUpdate(host, port, userName, password, dbName, formatSql);
        log.info("删除条数：{}", count);
    }


    /**
     * 读取excel insert to mysql
     *
     * @param excelPath
     * @param dbName
     * @param tableName
     * @throws IOException
     */
    public void execInsert(String excelPath, String dbName, String tableName) throws IOException {
        List<String> excelSqlData = readExcelSqlData(excelPath);
        String title = excelSqlData.get(0);
        String sql = "INSERT INTO " + dbName + "." + tableName + " (" + title + ")" + " VALUES (%s);";
        excelSqlData.remove(0);
        if (!excelSqlData.isEmpty()) {
            for (String item : excelSqlData) {
                String formatSql = String.format(sql, item);
                System.out.println(formatSql);
                try {
                    JdbcUtil.executeUpdate(host, port, userName, password, dbName, formatSql);
                } catch (SQLException e) {
                    log.error("sql 插入失败。。。。。。。。。。。。。。。。。。。");
                    e.printStackTrace();
                    System.exit(1);
                }
            }
        }


    }

    /**
     * 读取excel to list
     *
     * @param excelPath
     * @return
     * @throws IOException
     */
    public List<String> readExcelSqlData(String excelPath) throws IOException {
        File file = new File(excelPath);
        FileInputStream inputStream = new FileInputStream(file);
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0);
        List<String> list = new ArrayList<>();
        StringBuffer titleBuffer = new StringBuffer();
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            StringBuffer rowBuffer = new StringBuffer();
            if (null != row) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    if (j >= row.getLastCellNum()) {
                        break;
                    }
                    if (i == 0) {
                        titleBuffer.append("`" + cell + "`" + ",");
                        continue;
                    }
                    if (cell == null) {
                        cell = row.createCell(j);
                        cell.setCellValue("NULL");
                    }
                    if ("FALSE".equals(cell.toString()) || "TRUE".equals(cell.toString())) {
                        cell.setCellType(CellType.NUMERIC);
                    } else {
                        cell.setCellType(CellType.STRING);
                    }
                    rowBuffer.append("'" + cell + "'" + ",");
                }
            }

            if (i == 0) {
                String titleString = titleBuffer.substring(0, titleBuffer.length() - 1);
                list.add(titleString);
                continue;
            }
            String rowString = rowBuffer.substring(0, rowBuffer.length() - 1).replaceAll("'NULL'", "NULL");
//                    .replaceAll("TRUE", "1").replaceAll("FALSE", "0");
            list.add(rowString);
        }
        return list;
    }

    /**
     * hive 查询结果导入excel
     *
     * @param list
     * @return
     * @throws Exception
     */
    public String exportDataToExcel(List<Map<String, Object>> list) throws Exception {
        fileExists(filePath);
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");
        List<String> titleList = new ArrayList<>();
        for (int i = 0; i < 1; i++) {
            Map<String, Object> map = list.get(i);
            map.forEach((k, v) -> {
                titleList.add(k);
            });
        }
        // 表头
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < titleList.size(); j++) {
                Cell cell = row.createCell(j);
                cell.setCellValue(titleList.get(j));
            }
        }
        for (int i = 0; i < list.size(); i++) {
            Row row = sheet.createRow(i + 1);
            Collection<Object> values = list.get(i).values();
            ArrayList<Object> valueList = new ArrayList<>(values);

            for (int j = 0; j < values.size(); j++) {
                Cell cell = row.createCell(j);
                Object value = valueList.get(j);
                if (value == null) {
                    cell.setCellValue("NULL");
                } else if ("false".equals(value.toString()) || "False".equals(value.toString())) {
                    cell.setCellValue(0);
                } else if ("true".equals(value.toString()) || "TRUE".equals(value.toString())) {
                    cell.setCellValue(1);
                } else {
                    cell.setCellValue(value + "");
                }
            }
        }
        // 将工作簿内容写入到文件输出流中
        try {
            FileOutputStream outputStream = new FileOutputStream(filePath);
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return filePath;
    }

    public void fileExists(String path) {
        File file = new File(path);
        if (file.exists()) {
            file.delete();
        }

    }


}
